﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using X.Common;
using System.Text.RegularExpressions;

namespace X.Data.Access
{
    public delegate T DateReaderToModel<T>(NullableDataReader rdr);

    public abstract class DBHelper
    {/// <summary>
        /// 
        /// </summary>
        protected DBHelper() { }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="db"></param>
        /// <param name="dbCommand"></param>
        /// <param name="cmdParams"></param>
        public static void BuildDBParameter(Database db, DbCommand dbCommand, Parameters cmdParams)
        {
            foreach (Parameter parameter in cmdParams.Entries)
            {
                if (parameter.Direction == ParameterDirection.Input)
                {
                    db.AddInParameter(dbCommand, parameter.Name, parameter.DBType, parameter.Value);
                }
                else if (parameter.Direction == ParameterDirection.Output)
                {
                    db.AddOutParameter(dbCommand, parameter.Name, parameter.DBType, parameter.Size);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="strCommand"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(CommandType commandType, string strCommand, Parameters cmdParams)
        {
            return ExecuteDataSet(string.Empty, commandType, strCommand, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="strCommand"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string databaseName, CommandType commandType, string strCommand, Parameters cmdParams)
        {
            DbCommand storedProcCommand;
            Database db;

            if (string.Empty == databaseName)
            {
                db = DatabaseFactory.CreateDatabase();
            }
            else
            {
                db = DatabaseFactory.CreateDatabase(databaseName);
            }

            if (commandType == CommandType.StoredProcedure)
            {
                storedProcCommand = db.GetStoredProcCommand(strCommand);
            }
            else
            {
                storedProcCommand = db.GetSqlStringCommand(strCommand);
            }

            if (cmdParams != null)
            {
                BuildDBParameter(db, storedProcCommand, cmdParams);
            }

            return db.ExecuteDataSet(storedProcCommand);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSetProc(string storedProcName)
        {
            return ExecuteDataSet(CommandType.StoredProcedure, storedProcName, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSetProc(string storedProcName, Parameters cmdParams)
        {
            return ExecuteDataSet(CommandType.StoredProcedure, storedProcName, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSetSql(string strSql)
        {
            return ExecuteDataSet(CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSetSql(string strSql, Parameters cmdParams)
        {
            return ExecuteDataSet(CommandType.Text, strSql, cmdParams);
        }

        public static int ExecuteNonQuery(CommandType commandType, string strCommand, Parameters cmdParams)
        {
            return ExecuteNonQuery(string.Empty, commandType, strCommand, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="strCommand"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string databaseName, CommandType commandType, string strCommand, Parameters cmdParams)
        {
            DbCommand storedProcCommand;
            Database db;

            if (string.Empty == databaseName)
            {
                db = DatabaseFactory.CreateDatabase();
            }
            else
            {
                db = DatabaseFactory.CreateDatabase(databaseName);
            }

            if (commandType == CommandType.StoredProcedure)
            {
                storedProcCommand = db.GetStoredProcCommand(strCommand);
            }
            else
            {
                storedProcCommand = db.GetSqlStringCommand(strCommand);
            }

            if (cmdParams != null)
            {
                BuildDBParameter(db, storedProcCommand, cmdParams);
            }

            return db.ExecuteNonQuery(storedProcCommand);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryProc(string storedProcName)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, storedProcName, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryProc(string storedProcName, Parameters cmdParams)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, storedProcName, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static int ExecuteNonQuerySql(string strSql)
        {
            return ExecuteNonQuery(CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static int ExecuteNonQuerySql(string strSql, Parameters cmdParams)
        {
            return ExecuteNonQuery(CommandType.Text, strSql, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static bool ExecuteProc(string storedProcName)
        {
            return (ExecuteNonQueryProc(storedProcName) > 0);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static bool ExecuteProc(string storedProcName, Parameters cmdParams)
        {
            return (ExecuteNonQueryProc(storedProcName, cmdParams) > 0);
        }

        public static NullableDataReader ExecuteReader(CommandType commandType, string strCommand, Parameters cmdParams)
        {
            return ExecuteReader(string.Empty, commandType, strCommand, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="strCommand"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static NullableDataReader ExecuteReader(string databaseName, CommandType commandType, string strCommand, Parameters cmdParams)
        {
            DbCommand storedProcCommand;
            Database db;

            if (string.Empty == databaseName)
            {
                db = DatabaseFactory.CreateDatabase();
            }
            else
            {
                db = DatabaseFactory.CreateDatabase(databaseName);
            }

            if (commandType == CommandType.StoredProcedure)
            {
                storedProcCommand = db.GetStoredProcCommand(strCommand);
            }
            else
            {
                storedProcCommand = db.GetSqlStringCommand(strCommand);
            }

            if (cmdParams != null)
            {
                BuildDBParameter(db, storedProcCommand, cmdParams);
            }

            return new NullableDataReader(db.ExecuteReader(storedProcCommand));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static NullableDataReader ExecuteReaderProc(string storedProcName)
        {
            return ExecuteReader(CommandType.StoredProcedure, storedProcName, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static NullableDataReader ExecuteReaderProc(string storedProcName, Parameters cmdParams)
        {
            return ExecuteReader(CommandType.StoredProcedure, storedProcName, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static NullableDataReader ExecuteReaderSql(string strSql)
        {
            return ExecuteReader(CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static NullableDataReader ExecuteReaderSql(string strSql, Parameters cmdParams)
        {
            return ExecuteReader(CommandType.Text, strSql, cmdParams);
        }

        public static object ExecuteScalar(CommandType commandType, string strCommand, Parameters cmdParams)
        {
            return ExecuteScalar(string.Empty, commandType, strCommand, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="strCommand"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string databaseName, CommandType commandType, string strCommand, Parameters cmdParams)
        {
            DbCommand storedProcCommand;
            Database db;

            if (string.Empty == databaseName)
            {
                db = DatabaseFactory.CreateDatabase();
            }
            else
            {
                db = DatabaseFactory.CreateDatabase(databaseName);
            }

            if (commandType == CommandType.StoredProcedure)
            {
                storedProcCommand = db.GetStoredProcCommand(strCommand);
            }
            else
            {
                storedProcCommand = db.GetSqlStringCommand(strCommand);
            }

            if (cmdParams != null)
            {
                BuildDBParameter(db, storedProcCommand, cmdParams);
            }

            object objA = db.ExecuteScalar(storedProcCommand);

            if (!object.Equals(objA, null) && !object.Equals(objA, DBNull.Value))
            {
                return objA;
            }

            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static object ExecuteScalarProc(string storedProcName)
        {
            return ExecuteScalar(CommandType.StoredProcedure, storedProcName, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static object ExecuteScalarProc(string storedProcName, Parameters cmdParams)
        {
            return ExecuteScalar(CommandType.StoredProcedure, storedProcName, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static object ExecuteScalarSql(string strSql)
        {
            return ExecuteScalar(CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static object ExecuteScalarSql(string strSql, Parameters cmdParams)
        {
            return ExecuteScalar(CommandType.Text, strSql, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool ExecuteSql(string strSql)
        {
            return (ExecuteNonQuerySql(strSql) > 0);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static bool ExecuteSql(string strSql, Parameters cmdParams)
        {
            return (ExecuteNonQuerySql(strSql, cmdParams) > 0);
        }

        public static bool Exists(CommandType commandType, string strCommand, Parameters cmdParams)
        {
            return Exists(string.Empty, commandType, strCommand, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="strCommand"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static bool Exists(string databaseName, CommandType commandType, string strCommand, Parameters cmdParams)
        {
            DbCommand storedProcCommand;
            Database db;

            if (string.Empty == databaseName)
            {
                db = DatabaseFactory.CreateDatabase();
            }
            else
            {
                db = DatabaseFactory.CreateDatabase(databaseName);
            }

            if (commandType == CommandType.StoredProcedure)
            {
                storedProcCommand = db.GetStoredProcCommand(strCommand);
            }
            else
            {
                storedProcCommand = db.GetSqlStringCommand(strCommand);
            }

            if (cmdParams != null)
            {
                BuildDBParameter(db, storedProcCommand, cmdParams);
            }

            if (ObjectToInt32(db.ExecuteScalar(storedProcCommand)) <= 0)
            {
                return false;
            }

            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static bool ExistsProc(string storedProcName)
        {
            return Exists(CommandType.StoredProcedure, storedProcName, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static bool ExistsProc(string storedProcName, Parameters cmdParams)
        {
            return Exists(CommandType.StoredProcedure, storedProcName, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool ExistsSql(string strSql)
        {
            return Exists(CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static bool ExistsSql(string strSql, Parameters cmdParams)
        {
            return Exists(CommandType.Text, strSql, cmdParams);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strchar"></param>
        /// <returns></returns>
        public static string FilterBadChar(string strchar)
        {
            if (string.IsNullOrEmpty(strchar))
            {
                return "";
            }

            return strchar.Replace("'", "");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static int GetMaxId(string storedProcName)
        {
            return ObjectToInt32(ExecuteScalarProc(storedProcName));
        }

        public static int GetMaxId(string tableName, string fieldName)
        {
            return GetMaxId(string.Empty, tableName, fieldName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static int GetMaxId(string databaseName, string tableName, string fieldName)
        {
            Database database;

            if (string.Empty == databaseName)
            {
                database = DatabaseFactory.CreateDatabase();
            }
            else
            {
                database = DatabaseFactory.CreateDatabase(databaseName);
            }

            string query = "SELECT MAX(" + fieldName + ") FROM " + tableName;

            DbCommand sqlStringCommand = database.GetSqlStringCommand(query);

            return ObjectToInt32(database.ExecuteScalar(sqlStringCommand));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storedProcName"></param>
        /// <param name="cmdParams"></param>
        /// <param name="datareaderToModel"></param>
        /// <returns></returns>
        public static IList<T> GetModelListProc<T>(string storedProcName, Parameters cmdParams, DateReaderToModel<T> datareaderToModel)
        {
            List<T> list = new List<T>();

            using (NullableDataReader reader = ExecuteReaderProc(storedProcName, cmdParams))
            {
                if (reader.Read())
                {
                    list.Add(datareaderToModel(reader));
                }
            }

            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <param name="cmdParams"></param>
        /// <param name="datareaderToModel"></param>
        /// <returns></returns>
        public static IList<T> GetModelListSql<T>(string strSql, Parameters cmdParams, DateReaderToModel<T> datareaderToModel)
        {
            List<T> list = new List<T>();

            using (NullableDataReader reader = ExecuteReaderSql(strSql, cmdParams))
            {
                if (reader.Read())
                {
                    list.Add(datareaderToModel(reader));
                }
            }

            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storedProcName"></param>
        /// <param name="cmdParams"></param>
        /// <param name="model"></param>
        /// <param name="datareaderToModel"></param>
        /// <returns></returns>
        public static T GetModelProc<T>(string storedProcName, Parameters cmdParams, T model, DateReaderToModel<T> datareaderToModel)
        {
            using (NullableDataReader reader = ExecuteReaderProc(storedProcName, cmdParams))
            {
                if (reader.Read())
                {
                    model = datareaderToModel(reader);
                }
            }

            return model;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <param name="cmdParams"></param>
        /// <param name="model"></param>
        /// <param name="datareaderToModel"></param>
        /// <returns></returns>
        public static T GetModelSql<T>(string strSql, Parameters cmdParams, T model, DateReaderToModel<T> datareaderToModel)
        {
            using (NullableDataReader reader = ExecuteReaderSql(strSql, cmdParams))
            {
                if (reader.Read())
                {
                    model = datareaderToModel(reader);
                }
            }

            return model;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public static bool IsNumber(string input)
        {
            if (string.IsNullOrEmpty(input))
            {
                return false;
            }

            return DataValidator.IsNumberSign(input);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int ObjectToInt32(object value)
        {
            int result = 0;

            if ((!object.Equals(value, null) && !object.Equals(value, DBNull.Value)) && !int.TryParse(value.ToString(), out result))
            {
                result = 0;
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public static string ToNumber(string input)
        {
            if (string.IsNullOrEmpty(input))
            {
                return "0";
            }

            if (!Regex.IsMatch(input, "^[+-]?[0-9]+[.]?[0-9]*$"))
            {
                return "0";
            }

            return input;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public static string ToValidId(string input)
        {
            if (!string.IsNullOrEmpty(input))
            {
                string[] strArray = input.Split(new char[] { ',' });
                string str = "";

                for (int i = 0; i < strArray.GetLength(0); i++)
                {
                    if (IsNumber(strArray[i]))
                    {
                        str = str + strArray[i] + ",";
                    }
                }

                if (str.Length > 0)
                {
                    return str.Substring(0, str.Length - 1);
                }
            }

            return "0";
        }
    }

    public interface INullableReader
    {
        bool GetBoolean(string name);
        byte GetByte(string name);
        char GetChar(string name);
        DateTime GetDateTime(string name);
        decimal GetDecimal(string name);
        double GetDouble(string name);
        Guid GetGuid(string name);
        short GetInt16(string name);
        int GetInt32(string name);
        long GetInt64(string name);
        float GetSingle(string name);
        string GetString(string name);
        object GetValue(string name);
        bool IsDBNull(string name);
    }

    public sealed class NullableDataReader : IDataReader, IDisposable, IDataRecord, INullableReader
    {/// <summary>
        /// 
        /// </summary>
        private IDataReader reader;

        /// <summary>
        /// 
        /// </summary>
        private NullableDataReader()
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataReader"></param>
        public NullableDataReader(IDataReader dataReader)
        {
            reader = dataReader;
        }

        /// <summary>
        /// 
        /// </summary>
        public void Close()
        {
            reader.Close();
        }

        /// <summary>
        /// 
        /// </summary>
        public void Dispose()
        {
            if (reader != null)
            {
                reader.Dispose();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public bool GetBoolean(int i)
        {
            return reader.GetBoolean(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool GetBoolean(string name)
        {
            bool boolean = false;

            if (!IsDBNull(name))
            {
                boolean = GetBoolean(reader.GetOrdinal(name));
            }

            return boolean;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public byte GetByte(int i)
        {
            return reader.GetByte(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public byte GetByte(string name)
        {
            return GetByte(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <param name="fieldOffset"></param>
        /// <param name="buffer"></param>
        /// <param name="bufferoffset"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
        {
            return reader.GetBytes(i, fieldOffset, buffer, bufferoffset, length);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public char GetChar(int i)
        {
            return reader.GetChar(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public char GetChar(string name)
        {
            return GetChar(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <param name="fieldoffset"></param>
        /// <param name="buffer"></param>
        /// <param name="bufferoffset"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
        {
            return reader.GetChars(i, fieldoffset, buffer, bufferoffset, length);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public IDataReader GetData(int i)
        {
            return reader.GetData(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public string GetDataTypeName(int i)
        {
            return reader.GetDataTypeName(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public string GetDataTypeName(string name)
        {
            return reader.GetDataTypeName(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public DateTime GetDateTime(int i)
        {
            return reader.GetDateTime(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DateTime GetDateTime(string name)
        {
            if (IsDBNull(name))
            {
                return DateTime.Now;
            }

            return reader.GetDateTime(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public decimal GetDecimal(int i)
        {
            return reader.GetDecimal(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public decimal GetDecimal(string name)
        {
            decimal @decimal = 0M;

            if (!IsDBNull(name))
            {
                @decimal = reader.GetDecimal(reader.GetOrdinal(name));
            }

            return @decimal;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public double GetDouble(int i)
        {
            return reader.GetDouble(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public double GetDouble(string name)
        {
            double num = 0.0;

            if (!IsDBNull(name))
            {
                num = reader.GetDouble(reader.GetOrdinal(name));
            }

            return num;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public Type GetFieldType(int i)
        {
            return reader.GetFieldType(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public Type GetFieldType(string name)
        {
            return reader.GetFieldType(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public float GetFloat(int i)
        {
            return reader.GetFloat(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public Guid GetGuid(int i)
        {
            return reader.GetGuid(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public Guid GetGuid(string name)
        {
            return reader.GetGuid(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public short GetInt16(int i)
        {
            return reader.GetInt16(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public short GetInt16(string name)
        {
            if (IsDBNull(name))
            {
                return 0;
            }

            return reader.GetInt16(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public int GetInt32(int i)
        {
            return reader.GetInt32(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public int GetInt32(string name)
        {
            if (IsDBNull(name))
            {
                return 0;
            }

            return reader.GetInt32(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public long GetInt64(int i)
        {
            return reader.GetInt64(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public long GetInt64(string name)
        {
            if (IsDBNull(name))
            {
                return 0L;
            }

            return reader.GetInt64(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public string GetName(int i)
        {
            return reader.GetName(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DateTime? GetNullableDateTime(string name)
        {
            if (IsDBNull(name))
            {
                return null;
            }

            return new DateTime?(reader.GetDateTime(reader.GetOrdinal(name)));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public int GetOrdinal(string name)
        {
            return reader.GetOrdinal(name);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable GetSchemaTable()
        {
            return reader.GetSchemaTable();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public float GetSingle(string name)
        {
            float @float = 0f;

            if (!IsDBNull(name))
            {
                @float = reader.GetFloat(reader.GetOrdinal(name));
            }

            return @float;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public string GetString(int i)
        {
            return reader.GetString(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public string GetString(string name)
        {
            string str = string.Empty;

            if (!IsDBNull(name))
            {
                str = reader.GetString(reader.GetOrdinal(name));
            }

            return str;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public object GetValue(int i)
        {
            return reader.GetValue(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public object GetValue(string name)
        {
            return reader.GetValue(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="values"></param>
        /// <returns></returns>
        public int GetValues(object[] values)
        {
            return reader.GetValues(values);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public bool IsDBNull(int i)
        {
            return reader.IsDBNull(i);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool IsDBNull(string name)
        {
            return reader.IsDBNull(reader.GetOrdinal(name));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool NextResult()
        {
            return reader.NextResult();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool Read()
        {
            return reader.Read();
        }

        /// <summary>
        /// 
        /// </summary>
        public int Depth
        {
            get
            {
                return reader.Depth;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public int FieldCount
        {
            get
            {
                return reader.FieldCount;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public bool IsClosed
        {
            get
            {
                return reader.IsClosed;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="i"></param>
        /// <returns></returns>
        public object this[int i]
        {
            get
            {
                return reader[i];
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public object this[string name]
        {
            get
            {
                return reader[name];
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public int RecordsAffected
        {
            get
            {
                return reader.RecordsAffected;
            }
        }
    }

    public class Parameter
    {
        private DbType m_DBType;
        private ParameterDirection m_Direction;
        private string m_Name;
        private int m_Size;
        private object m_Value;

        /// <summary>
        /// 
        /// </summary>
        public Parameter()
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <param name="value"></param>
        public Parameter(string name, DbType type, object value)
            : this(ParameterDirection.Input, name, type, value, 0)
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="direction"></param>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <param name="size"></param>
        public Parameter(ParameterDirection direction, string name, DbType type, object value, int size)
        {
            m_Direction = direction;
            m_Name = name;
            m_DBType = type;
            m_Value = value;
            m_Size = size;
        }

        /// <summary>
        /// 
        /// </summary>
        public DbType DBType
        {
            get
            {
                return m_DBType;
            }
            set
            {
                m_DBType = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public ParameterDirection Direction
        {
            get
            {
                return m_Direction;
            }
            set
            {
                m_Direction = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public string Name
        {
            get
            {
                return m_Name;
            }
            set
            {
                m_Name = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public int Size
        {
            get
            {
                return m_Size;
            }
            set
            {
                m_Size = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public object Value
        {
            get
            {
                return m_Value;
            }
            set
            {
                m_Value = value;
            }
        }
    }

    public class Parameters
    {
        private IList<Parameter> m_Entries;

        /// <summary>
        /// 
        /// </summary>
        public Parameters()
        {
            m_Entries = new List<Parameter>();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <param name="value"></param>
        public Parameters(string name, DbType type, object value)
        {
            m_Entries = new List<Parameter>();
            m_Entries.Add(new Parameter(name, type, value));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <param name="value"></param>
        public void AddInParameter(string name, DbType type, object value)
        {
            m_Entries.Add(new Parameter(name, type, value));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <param name="size"></param>
        public void AddOutParameter(string name, DbType type, int size)
        {
            m_Entries.Add(new Parameter(ParameterDirection.Output, name, type, null, size));
        }

        /// <summary>
        /// 
        /// </summary>
        public IList<Parameter> Entries
        {
            get
            {
                return m_Entries;
            }
            set
            {
                m_Entries = value;
            }
        }
    }
}
